﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using StudentManage.Data;

namespace StudentManage.Web.Admin.Management.SysManage
{
    public partial class DataSyn : SecurityPage
    {
        public override string PrivCode()
        {
            return "e56cd197be997e51";
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack) return;
        }

        //学院
        protected void Btn_UpLoadXY_Click(object sender, EventArgs e)
        {
            if (this.FileXY.HasFile == false)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert('请您选择Excel文件')</script>");
                return;
            }

            string IsXls = System.IO.Path.GetExtension(FileXY.FileName).ToString().ToLower();
            if (IsXls != ".xls")
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert('只可以选择Excel文件')</script>");
                return;
            }

            string filename = FileXY.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileXY.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("XYDM",typeof(string));
                dt.Columns.Add("XYMC", typeof(string));
                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["XYDM"] = dr[i]["XYDM"].ToString();
                    row["XYMC"] = dr[i]["XYMC"].ToString();
                    dt.Rows.Add(row);
                }

                this.ClearData("xydm");
                this.SqlBulkCopyInsert(dt, "xydm");
                this.Lab_UpLoadXY.Text = "导入成功:" + dr.Length + "条";
            }
        }

        //校区
        protected void Btn_UpLoadXQ_Click(object sender, EventArgs e)
        {
            string filename = FileXQ.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileXQ.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("XQDM", typeof(string));
                dt.Columns.Add("XQMC", typeof(string));

                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["XQDM"] = dr[i]["XQDM"].ToString();
                    row["XQMC"] = dr[i]["XQMC"].ToString();
                    dt.Rows.Add(row);
                }
                this.ClearData("xqdm");
                this.SqlBulkCopyInsert(dt, "xqdm");
                this.Lab_UpLoadXQ.Text = "导入成功:" + dr.Length + "条";
            }
        }

        //部门
        protected void Btn_UpLoadBM_Click(object sender, EventArgs e)
        {
            string filename = FileBM.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileBM.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("BMDM", typeof(string));
                dt.Columns.Add("BMMC", typeof(string));
                dt.Columns.Add("BMFQID", typeof(string));
                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["BMDM"] = dr[i]["BMDM"].ToString();
                    row["BMMC"] = dr[i]["BMMC"].ToString();
                    row["BMFQID"] = dr[i]["BMFQID"].ToString();
                    dt.Rows.Add(row);
                }
                this.ClearData("xy_bmxbm");
                this.SqlBulkCopyInsert(dt, "xy_bmxbm");
                this.Lab_UpLoadBM.Text = "导入成功:" + dr.Length + "条";
            }
        }

        //班级
        protected void Btn_UpLoadBJ_Click(object sender, EventArgs e)
        {
            string filename = FileBJ.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileBJ.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("BJMC", typeof(string));
                dt.Columns.Add("BJDM", typeof(string));
                dt.Columns.Add("XYMC", typeof(string));
                dt.Columns.Add("XYDM", typeof(string));
                dt.Columns.Add("NJ", typeof(string));
                dt.Columns.Add("SSZYDM", typeof(string));
                dt.Columns.Add("ZYMC", typeof(string));
                dt.Columns.Add("XZ", typeof(string));
                dt.Columns.Add("CC", typeof(string));
                dt.Columns.Add("SFBY", typeof(string));

                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["BJMC"] = dr[i]["BJMC"].ToString();
                    row["BJDM"] = dr[i]["BJDM"].ToString();
                    row["XYMC"] = dr[i]["XYMC"].ToString();
                    row["XYDM"] = dr[i]["XYDM"].ToString();
                    row["NJ"] = dr[i]["NJ"].ToString();
                    row["SSZYDM"] = dr[i]["SSZYDM"].ToString();
                    row["ZYMC"] = dr[i]["ZYMC"].ToString();
                    row["XZ"] = dr[i]["XZ"].ToString();
                    row["CC"] = dr[i]["CC"].ToString();
                    row["SFBY"] = dr[i]["SFBY"].ToString();
                    dt.Rows.Add(row);
                }
                this.ClearData("xy_bj");
                this.SqlBulkCopyInsert(dt, "xy_bj");
                this.Lab_UpLoadBJ.Text = "导入成功:" + dr.Length + "条";
            }
        }

        //教工
        protected void Btn_UpLoadJG_Click(object sender, EventArgs e)
        {
            string filename = FileJG.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileJG.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("ZGH", typeof(string));
                dt.Columns.Add("XM", typeof(string));
                dt.Columns.Add("XB", typeof(string));
                dt.Columns.Add("XYMC", typeof(string));
                dt.Columns.Add("BMMC", typeof(string));
                dt.Columns.Add("BMDM", typeof(string));
                dt.Columns.Add("CARDID", typeof(int));

                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["ZGH"] = dr[i]["ZGH"].ToString();
                    row["XM"] = dr[i]["XM"].ToString();
                    row["XB"] = dr[i]["XB"].ToString();
                    row["XYMC"] = dr[i]["XYMC"].ToString();
                    row["BMMC"] = dr[i]["BMMC"].ToString();
                    row["BMDM"] = dr[i]["BMDM"].ToString();
                    row["CARDID"] = 0;
                    dt.Rows.Add(row);
                }
                this.ClearData("xy_jgxx");
                this.SqlBulkCopyInsert(dt, "xy_jgxx");
                this.Lab_UpLoadJG.Text = "导入成功:" + dr.Length + "条";
            }
        }

        //学生
        protected void Btn_UpLoadXS_Click(object sender, EventArgs e)
        {
            string filename = FileXS.FileName;
            string savePath = Server.MapPath(("~\\Upload\\files\\") + filename);
            FileXS.SaveAs(savePath);
            DataSet ds = ExecleDs(savePath, filename, "Oracle SQL Handler Export");
            if (ds.Tables[0].Rows.Count == 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script>alert(''Excel表为空表,无数据!')</script>");
            }
            else
            {
                DataRow[] dr = ds.Tables[0].Select();
                DataTable dt = new DataTable();
                dt.Columns.Add("XH", typeof(string));
                dt.Columns.Add("XM", typeof(string));
                dt.Columns.Add("XB", typeof(string));
                dt.Columns.Add("ZYMC", typeof(string));
                dt.Columns.Add("ZYDM", typeof(string));
                dt.Columns.Add("XZB", typeof(string));
                dt.Columns.Add("BJDM", typeof(string));
                dt.Columns.Add("XY", typeof(string));
                dt.Columns.Add("XYDM", typeof(string));
                dt.Columns.Add("NJ", typeof(int));
                dt.Columns.Add("XZ", typeof(string));
                dt.Columns.Add("XL", typeof(string));
                dt.Columns.Add("XJZT", typeof(string));
                dt.Columns.Add("CARDID", typeof(int));

                for (int i = 0; i < dr.Length; i++)
                {
                    DataRow row = dt.NewRow();
                    row["XH"] = dr[i]["XH"].ToString();
                    row["XM"] = dr[i]["XM"].ToString();
                    row["XB"] = dr[i]["XB"].ToString();
                    row["ZYMC"] = dr[i]["ZYMC"].ToString();
                    row["ZYDM"] = "";
                    row["XZB"] = dr[i]["XZB"].ToString();
                    row["BJDM"] = "";
                    row["XY"] = dr[i]["XY"].ToString();
                    row["XYDM"] = "";//dr[i]["XYDM"].ToString();
                    row["NJ"] = Convert.ToInt32(dr[i]["NJ"].ToString() == "" ? "0" : dr[i]["NJ"].ToString());
                    row["XZ"] = dr[i]["XZ"].ToString();
                    row["XL"] = dr[i]["XL"].ToString();
                    row["XJZT"] = dr[i]["XJZT"].ToString();
                    row["CARDID"] = 0;

                    dt.Rows.Add(row);
                }
                this.ClearData("xy_xsxx");
                this.SqlBulkCopyInsert(dt, "xy_xsxx");
                this.Lab_UpLoadXS.Text = "导入成功:" + dr.Length + "条";
            }
        }

        public DataSet ExecleDs(string filenameurl, string table,string Etablename)
        {

            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+Etablename+"$]", conn);
            odda.Fill(ds, table);
            return ds;
        }

        /// <summary> 
        /// 使用SqlBulkCopy方式插入数据 
        /// </summary>
        public void SqlBulkCopyInsert(DataTable insertdt, string TableName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString();
            DataTable dataTable = insertdt;
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = TableName;//设置需要插入数据表名
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;//设置批量插入数据的最大数据量的值
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();
        }

        /// <summary>
        /// 清空数据
        /// </summary>
        public void ClearData(string TableName)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand("Delete from "+TableName,con);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}